//
// (C) Copyright 2009 Irantha Suwandarathna (irantha@gmail.com)
// All rights reserved.
//
/* Copyright (c) 1995-2000, The Hypersonic SQL Group.
 * All rights reserved.
 *
 * Redistribution and use _in source and binary forms, with or without
 * modification, are permitted provided that the following conditions are met:
 *
 * Redistributions of source code must retain the above copyright notice, this
 * list of conditions and the following disclaimer.
 *
 * Redistributions _in binary form must reproduce the above copyright notice,
 * this list of conditions and the following disclaimer _in the documentation
 * and/or other materials provided with the distribution.
 *
 * Neither the name of the Hypersonic SQL Group nor the names of its
 * contributors may be used to endorse or promote products derived from this
 * software without specific prior written permission.
 *
 * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
 * AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
 * IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
 * ARE DISCLAIMED. IN NO EVENT SHALL THE HYPERSONIC SQL GROUP,
 * OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL,
 * EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO,
 * PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
 * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
 * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
 * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
 * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
 *
 * This software consists of voluntary contributions made by many individuals
 * on behalf of the Hypersonic SQL Group.
 *
 *
 * For work added by the HSQL Development Group:
 *
 * Copyright (c) 2001-2008, The HSQL Development Group
 * All rights reserved.
 *
 * Redistribution and use _in source and binary forms, with or without
 * modification, are permitted provided that the following conditions are met:
 *
 * Redistributions of source code must retain the above copyright notice, this
 * list of conditions and the following disclaimer.
 *
 * Redistributions _in binary form must reproduce the above copyright notice,
 * this list of conditions and the following disclaimer _in the documentation
 * and/or other materials provided with the distribution.
 *
 * Neither the name of the HSQL Development Group nor the names of its
 * contributors may be used to endorse or promote products derived from this
 * software without specific prior written permission.
 *
 * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
 * AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
 * IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
 * ARE DISCLAIMED. IN NO EVENT SHALL HSQL DEVELOPMENT GROUP, HSQLDB.ORG,
 * OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL,
 * EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO,
 * PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
 * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
 * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
 * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
 * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
 */


using System;
using System.Collections.Generic;
using System.Text;
using System.Linq;
using System.Data;
using EffiProzDB.Lib;
using EffiProzDB.Persist;


namespace EffiProzDB
{


    // fredt@users 20020210 - patch 513005 by sqlbob@users (RMP) - ABS function
    // fredt@users 20020305 - patch 1.7.0 - change to 2D string arrays
    // sqlbob@users 20020420- patch 1.7.0 - added HEXTORAW and RAWTOHEX.
    // boucherb@user 20020918 - doc 1.7.2 - added JavaDoc  and code comments
    // fredt@user 20021021 - doc 1.7.2 - modified JavaDoc
    // boucherb@users 20030201 - patch 1.7.2 - direct calls for EffiProzDB.Library
    // fredt@users - patch 1.8.0 - new functions added

    /**
     * fredt - todo - since the introduction of SQL built-in functions and
     * evaluation of several session-dependent methods outside this class,
     * several methods here are dummies. These methods are still reported in
     * system tables incorrectly as corresponding to the SQL function names.
     */

    /**
     * Provides the HSQLDB implementation of standard Open Group SQL CLI
     * <em>Extended Scalar Functions</em> and other public HSQLDB SQL functions.<p>
     *
     * All methods here that have a Connection parameter are dummies and should
     * not be called from user supplied Java procedure or trigger code. Use real
     * SQL functions should be called instead _in these instances.
     *
     * Extensively rewritten and extended _in successive versions of HSQLDB.
     *
     * @author Thomas Mueller (Hypersonic SQL Group)
     * @version 1.8.0
     * @since Hypersonic SQL
     */
    public class Library
    {

        //static const SimpleDateFormat tocharFormat = new SimpleDateFormat();
        //static const SimpleDateFormat daynameFormat = new SimpleDateFormat("EEEE",
        //    Locale.ENGLISH);
        //static const SimpleDateFormat monthnameFormat =
        //    new SimpleDateFormat("MMMM", Locale.ENGLISH);
        //static const StringBuilder daynameBuffer   = new StringBuilder();
        //static const StringBuilder monthnameBuffer = new StringBuilder();
        //static const FieldPosition monthPosition =
        //    new FieldPosition(SimpleDateFormat.MONTH_FIELD);
        //static const FieldPosition dayPosition =
        //    new FieldPosition(SimpleDateFormat.DAY_OF_WEEK_FIELD);
        public static readonly String[,] sNumeric = {
        {
            "ABS", "EffiProzDB.Library.Abs"
        }, {
            "ACOS", "System.Math.Acos"
        }, {
            "ASIN", "System.Math.Asin"
        }, {
            "ATAN", "System.Math.Atan"
        }, {
            "ATAN2", "System.Math.Atan2"
        }, {
            "CEILING", "System.Math.Ceil"
        }, {
            "COS", "System.Math.Cos"
        }, {
            "COT", "EffiProzDB.Library.cot"
        }, {
            "DEGREES", "System.Math.ToDegrees"
        }, {
            "EXP", "System.Math.Exp"
        }, {
            "FLOOR", "System.Math.Floor"
        }, {
            "LOG", "System.Math.Log"
        }, {
            "LOG10", "EffiProzDB.Library.log10"
        }, {
            "MOD", "EffiProzDB.Library.Mod"
        }, {
            "PI", "EffiProzDB.Library.pi"
        }, {
            "POWER", "System.Math.Pow"
        }, {
            "RADIANS", "System.Math.toRadians"
        }, {
            "RAND", "System.Math.random"
        }, {
            "ROUND", "EffiProzDB.Library.round"
        }, {
            "SIGN", "EffiProzDB.Library.sign"
        }, {
            "SIN", "System.Math.sin"
        }, {
            "SQRT", "System.Math.sqrt"
        }, {
            "TAN", "System.Math.tan"
        }, {
            "TRUNCATE", "EffiProzDB.Library.truncate"
        }, {
            "BITAND", "EffiProzDB.Library.bitand"
        }, {
            "BITOR", "EffiProzDB.Library.bitor"
        }, {
            "BITXOR", "EffiProzDB.Library.bitxor"
        }, {
            "ROUNDMAGIC", "EffiProzDB.Library.roundMagic"
        }
    };

        // fredt@users 20010701 - patch 418023 by deforest@users
        // the definition for SUBSTR was added
        public static readonly String[,] sString = {
        {
            "ASCII", "EffiProzDB.Library.ascii"
        }, {
            "BIT_LENGTH", "EffiProzDB.Library.bitLength"
        }, {
            "CHAR", "EffiProzDB.Library.character"
        }, {
            "CHAR_LENGTH", "EffiProzDB.Library.Length"
        }, {
            "CHARACTER_LENGTH", "EffiProzDB.Library.Length"
        }, {
            "CONCAT", "EffiProzDB.Library.concat"
        }, {
            "DIFFERENCE", "EffiProzDB.Library.difference"
        }, {
            "HEXTORAW", "EffiProzDB.Library.hexToRaw"
        }, {
            "INSERT", "EffiProzDB.Library.insert"
        }, {
            "LCASE", "EffiProzDB.Library.lcase"
        }, {
            "LEFT", "EffiProzDB.Library.left"
        }, {
            "LENGTH", "EffiProzDB.Library.Length"
        }, {
            "LOCATE", "EffiProzDB.Library.locate"
        }, {
            "LTRIM", "EffiProzDB.Library.ltrim"
        }, {
            "OCTET_LENGTH", "EffiProzDB.Library.octetLength"
        }, {
            "RAWTOHEX", "EffiProzDB.Library.rawToHex"
        }, {
            "REPEAT", "EffiProzDB.Library.repeat"
        }, {
            "REPLACE", "EffiProzDB.Library.replace"
        }, {
            "RIGHT", "EffiProzDB.Library.right"
        }, {
            "RTRIM", "EffiProzDB.Library.rtrim"
        }, {
            "SOUNDEX", "EffiProzDB.Library.soundex"
        }, {
            "SPACE", "EffiProzDB.Library.space"
        }, {
            "SUBSTR", "EffiProzDB.Library.Substring"
        }, {
            "SUBSTRING", "EffiProzDB.Library.Substring"
        }, {
            "UCASE", "EffiProzDB.Library.ucase"
        }, {
            "LOWER", "EffiProzDB.Library.lcase"
        }, {
            "UPPER", "EffiProzDB.Library.ucase"
        }
    };
        public static readonly String[,] sTimeDate = {
        {
            "CURDATE", "EffiProzDB.Library.curdate"
        }, {
            "CURTIME", "EffiProzDB.Library.curtime"
        }, {
            "DATEDIFF", "EffiProzDB.Library.datediff"
        }, {
            "DAYNAME", "EffiProzDB.Library.dayname"
        }, {
            "DAY", "EffiProzDB.Library.dayofmonth"
        }, {
            "DAYOFMONTH", "EffiProzDB.Library.dayofmonth"
        }, {
            "DAYOFWEEK", "EffiProzDB.Library.dayofweek"
        }, {
            "DAYOFYEAR", "EffiProzDB.Library.dayofyear"
        }, {
            "HOUR", "EffiProzDB.Library.hour"
        }, {
            "MINUTE", "EffiProzDB.Library.minute"
        }, {
            "MONTH", "EffiProzDB.Library.month"
        }, {
            "MONTHNAME", "EffiProzDB.Library.monthname"
        }, {
            "NOW", "EffiProzDB.Library.now"
        }, {
            "QUARTER", "EffiProzDB.Library.quarter"
        }, {
            "SECOND", "EffiProzDB.Library.second"
        }, {
            "WEEK", "EffiProzDB.Library.week"
        }, {
            "YEAR", "EffiProzDB.Library.year"
        }, {
            "TO_CHAR", "EffiProzDB.Library.to_char"
        }
    };

        public static readonly String[,] sSystem = {
        {
            "DATABASE", "EffiProzDB.Library.database"
        }, {
            "USER", "EffiProzDB.Library.user"
        }, {
            "IDENTITY", "EffiProzDB.Library.identity"
        }
    };

        private Library() { }

        public static Dictionary<string, string> getAliasMap()
        {

            Dictionary<string, string> h = new Dictionary<string, string>(83);

            register(h, sNumeric);
            register(h, sString);
            register(h, sTimeDate);
            register(h, sSystem);

            return h;
        }

        private static void register(Dictionary<string, string> h, string[,] s)
        {

            for (int i = 0; i < s.GetLength(0); i++)
            {
                h.Add(s[i, 0], s[i, 1]);
            }
        }

        private static Random rRandom = new Random();

        // NUMERIC FUNCTIONS
        // fredt@users 20020220 - patch 489184 by xclayl@users - thread safety

        /**
         * Returns the next pseudorandom, uniformly distributed <code>double</code> value
         * between 0.0 and 1.0 from a single, system-wide random number generator's
         * sequence, optionally re-seeding (and thus resetting) the generator sequence.
         *
         * If the seed value is <code>null</code>, then the underlying random number
         * generator retrieves the next value _in its current sequence, else the seed
         * alters the state of the generator object so as to be _in exactly the same state
         * as if it had just been created with the seed value.
         * @param seed an optional parameter with which to reseed the underlying
         * pseudorandom number generator
         * @return the next pseudorandom, uniformly distributed <code>double</code> value between
         *      0.0 and 1.0
         */
        public static double rand(int seed)
        {

            // boucherb@users 20020918
            // CHECKME: perhaps rRandom should be a member of Session,
            // since otherwise connections are *not* guranteed to get the
            // same pseudorandom sequence, given the same set of calls to this
            // SQL function.  This makes comparitive analysis difficult.
            // In fact, rRandom will be shared across multiple in-process
            // database instances, so it is not even guaranteed that the
            // sole connection to one instance will get the same sequence given
            // the same set of calls to this SQL function.
            lock (rRandom)
            {
                if (seed != -1)
                {
                    rRandom = new Random(seed);
                }

                return rRandom.NextDouble();
            }
        }

        /**
         * Returns the absolute value of the given <code>double</code> value.
         * @param d the number for which to determine the absolute value
         * @return the absolute value of <code>d</code>, as a <code>double</code>
         */
        public static double abs(double d)
        {
            return Math.Abs(d);
        }

        // this magic number works for 100000000000000; but not for 0.1 and 0.01
        private const double LOG10_FACTOR = 0.43429448190325183;

        /**
         * Returns the base 10 logarithm of the given <code>double</code> value.
         * @param x the value for which to calculate the base 10 logarithm
         * @return the base 10 logarithm of <code>x</code>, as a <code>double</code>
         */
        public static double log10(double x)
        {
            return roundMagic(Math.Log(x) * LOG10_FACTOR);
        }

        /**
         * Retrieves a <em>magically</em> rounded </code>double</code> value produced
         * from the given <code>double</code> value.  This method provides special
         * handling for numbers close to zero and performs rounding only for
         * numbers within a specific range, returning  precisely the given value
         * if it does not lie _in this range. <p>
         *
         * Special handling includes: <p>
         *
         * <UL>
         * <LI> input _in the interval -0.0000000000001..0.0000000000001 returns 0.0
         * <LI> input outside the interval -1000000000000..1000000000000 returns
         *      input unchanged
         * <LI> input is converted to string form
         * <LI> input with a <code>String</code> form length greater than 16 returns
         *      input unchaged
         * <LI> <code>String</code> form with last four characters of '...000x' where
         *      x != '.' is converted to '...0000'
         * <LI> <code>String</code> form with last four characters of '...9999' is
         *      converted to '...999999'
         * <LI> the <code>java.lang.Double.doubleValue</code> of the <code>String</code>
         *      form is returned
         * </UL>
         * @param d the double value for which to retrieve the <em>magically</em>
         *      rounded value
         * @return the <em>magically</em> rounded value produced
         */
        public static double roundMagic(double d)
        {

            // this function rounds numbers _in a good way but slow:
            // - special handling for numbers around 0
            // - only numbers <= +/-1000000000000
            // - convert to a string
            // - check the last 4 characters:
            // '000x' becomes '0000'
            // '999x' becomes '999999' (this is rounded automatically)
            if ((d < 0.0000000000001) && (d > -0.0000000000001))
            {
                return 0.0;
            }

            if ((d > 1000000000000.0) || (d < -1000000000000.0))
            {
                return d;
            }

            StringBuilder s = new StringBuilder();

            s.Append(d);

            int len = s.Length;

            if (len < 16)
            {
                return d;
            }

            char cx = s[(len - 1)];
            char c1 = s[(len - 2)];
            char c2 = s[(len - 3)];
            char c3 = s[(len - 4)];

            if ((c1 == '0') && (c2 == '0') && (c3 == '0') && (cx != '.'))
            {
                s[len - 1] = '0';
            }
            else if ((c1 == '9') && (c2 == '9') && (c3 == '9') && (cx != '.'))
            {
                s[len - 1] = '9';
                s.Append('9');
                s.Append('9');
            }

            return Double.Parse(s.ToString());
        }

        /**
         * Returns the cotangent of the given <code>double</code> value
         *  expressed _in radians.
         * @param d the angle, expressed _in radians
         * @return the cotangent
         */
        public static double cot(double d)
        {
            return 1.0 / Math.Tan(d);
        }

        /**
         * Returns the remainder (modulus) of the first given integer divided
         * by the second. <p>
         *
         * @param i1 the numerator
         * @param i2 the divisor
         * @return <code>i1</code> % <code>i2</code>, as an <code>int</code>
         */
        public static int mod(int i1, int i2)
        {
            return i1 % i2;
        }

        /**
         * Returns the constant value, pi.
         * @return pi as a <code>double</code> value
         */
        public static double pi()
        {
            return Math.PI;
        }

        /**
         * Returns the given <code>double</code> value, rounded to the given
         * <code>int</code> places right of the decimal point. If
         * the supplied rounding place value is negative, rounding is performed
         * to the left of the decimal point, using its magnitude (absolute value).
         * @param d the value to be rounded
         * @param p the rounding place value
         * @return <code>d</code> rounded
         */
        public static double round(double d, int p)
        {

            double f = Math.Pow(10.0, p);

            return Math.Round(d * f) / f;
        }

        /**
         * Returns an indicator of the sign of the given <code>double</code>
         * value. If the value is less than zero, -1 is returned. If the value
         * equals zero, 0 is returned. If the value is greater than zero, 1 is
         * returned.
         * @param d the value
         * @return the sign of <code>d</code>
         */
        public static int sign(double d)
        {

            return (d < 0) ? -1
                           : ((d > 0) ? 1
                                      : 0);
        }

        /**
         * Returns the given <code>double</code> value, truncated to
         * the given <code>int</code> places right of the decimal point.
         * If the given place value is negative, the given <code>double</code>
         * value is truncated to the left of the decimal point, using the
         * magnitude (aboslute value) of the place value.
         * @param d the value to truncate
         * @param p the places left or right of the decimal point at which to
         *          truncate
         * @return <code>d</code>, truncated
         */
        public static double truncate(double d, int p)
        {

            double f = Math.Pow(10.0, p);
            double g = d * f;

            return ((d < 0) ? Math.Ceiling(g)
                            : Math.Floor(g)) / f;
        }

        /**
         * Returns the bit-wise logical <em>and</em> of the given
         * integer values.
         * @param i the first value
         * @param j the second value
         * @return the bit-wise logical <em>and</em> of
         *      <code>i</code> and <code>j</code>
         */
        public static int bitand(int i, int j)
        {
            return i & j;
        }

        /**
         * Returns the bit-wise logical <em>or</em> of the given
         * integer values.
         *
         * @param i the first value
         * @param j the second value
         * @return the bit-wise logical <em>or</em> of
         *      <code>i</code> and <code>j</code>
         */
        public static int bitor(int i, int j)
        {
            return i | j;
        }

        /**
         * Returns the bit-wise logical <em>xor</em> of the given
         * integer values.
         *
         * @param i the first value
         * @param j the second value
         * @return the bit-wise logical <em>xor</em> of
         *      <code>i</code> and <code>j</code>
         *
         * @since 1.8.0
         */
        public static int bitxor(int i, int j)
        {
            return i ^ j;
        }

        // STRING FUNCTIONS

        /**
         * Returns the Unicode code value of the leftmost character of
         * <code>s</code> as an <code>int</code>.  This is the same as the
         * ASCII value if the string contains only ASCII characters.
         * @param s the <code>String</code> to evaluate
         * @return the integer Unicode value of the
         *    leftmost character
         */
        public static int ascii(string s)
        {

            if ((s == null) || (s.Length == 0))
            {
                return 0;
            }

            return s[0];
        }

        /**
         * Returns the character string corresponding to the given ASCII
         * (or Unicode) value.
         *
         * <b>Note:</b> <p>
         *
         * In some SQL CLI
         * implementations, a <code>null</code> is returned if the range is outside 0..255.
         * In HSQLDB, the corresponding Unicode character is returned
         * unchecked.
         * @param code the character code for which to return a String
         *      representation
         * @return the string representation of the character
         */
        public static string character(int code)
        {
            return ((char)code).ToString();
        }

        /**
         * Returns a <code>String</code> object that is the result of an
         * concatenation of the given <code>String</code> objects. <p>
         *
         * <b>When only one string is NULL, the result is different from that
         * returned by an (string1 || string2) expression:
         *
         * <UL>
         * <LI> if both <code>String</code> objects are <code>null</code>, return
         *      <code>null</code>
         * <LI> if only one string is <code>null</code>, return the other
         * <LI> if both <code>String</code> objects are non-null, return as a
         *      <code>String</code> object the character sequence obtained by listing,
         *      _in left to right order, the characters of the first string followed by
         *      the characters of the second
         * </UL>
         * @param s1 the first <code>String</code>
         * @param s2 the second <code>String</code>
         * @return <code>s1</code> concatentated with <code>s2</code>
         */
        public static string concat(string s1, string s2)
        {

            if (s1 == null)
            {
                if (s2 == null)
                {
                    return null;
                }

                return s2;
            }

            if (s2 == null)
            {
                return s1;
            }

            return s1 + s2;
        }

        /**
         * Returns a count of the characters that do not match when comparing
         * the 4 digit numeric SOUNDEX character sequences for the
         * given <code>String</code> objects.  If either <code>String</code> object is
         * <code>null</code>, zero is returned.
         * @param s1 the first <code>String</code>
         * @param s2 the second <code>String</code>
         * @return the number of differences between the <code>SOUNDEX</code> of
         *      <code>s1</code> and the <code>SOUNDEX</code> of <code>s2</code>
         */

        // fredt@users 20020305 - patch 460907 by fredt - soundex
        public static int difference(string s1, string s2)
        {

            // todo: check if this is the standard algorithm
            if ((s1 == null) || (s2 == null))
            {
                return 0;
            }

            s1 = soundex(s1);
            s2 = soundex(s2);

            int e = 0;

            for (int i = 0; i < 4; i++)
            {
                if (s1[(i)] != s2[(i)])
                {
                    e++;
                }
            }

            return e;
        }

        /**
         * Converts a <code>String</code> of hexidecimal digit characters to a raw
         * binary value, represented as a <code>String</code>.<p>
         *
         * The given <code>String</code> object must consist of a sequence of
         * 4 digit hexidecimal character substrings.<p> If its length is not
         * evenly divisible by 4, <code>null</code> is returned.  If any of
         * its 4 character subsequences cannot be parsed as a
         * 4 digit, base 16 value, then a NumberFormatException is thrown.
         *
         * This conversion has the effect of reducing the character count 4:1.
         *
         * @param s a <code>String</code> of hexidecimal digit characters
         * @return an equivalent raw binary value, represented as a
         *      <code>String</code>
         */
        public static string hexToRaw(string s)
        {

            if (s == null)
            {
                return null;
            }

            char raw;
            StringBuilder to = new StringBuilder();
            int len = s.Length;

            if (len % 4 != 0)
            {
                return null;
            }

            for (int i = 0; i < len; i += 4)
            {
                raw = (char)int.Parse(s.Substring(i, i + 4 - (i)), System.Globalization.NumberStyles.HexNumber);

                to.Append(raw);
            }

            return (to.ToString());
        }

        /**
         * Returns a character sequence which is the result of writing the
         * first <code>length</code> number of characters from the second
         * given <code>String</code> over the first string. The start position
         * _in the first string where the characters are overwritten is given by
         * <code>start</code>.<p>
         *
         * <b>Note:</b> In order of precedence, boundry conditions are handled as
         * follows:<p>
         *
         * <UL>
         * <LI>if either supplied <code>String</code> is null, then the other is
         *      returned; the check starts with the first given <code>String</code>.
         * <LI>if <code>start</code> is less than one, <code>s1</code> is returned
         * <LI>if <code>length</code> is less than or equal to zero,
         *     <code>s1</code> is returned
         * <LI>if the length of <code>s2</code> is zero, <code>s1</code> is returned
         * <LI>if <code>start</code> is greater than the length of <code>s1</code>,
         *      <code>s1</code> is returned
         * <LI>if <code>length</code> is such that, taken together with
         *      <code>start</code>, the indicated interval extends
         *      beyond the end of <code>s1</code>, then the insertion is performed
         *      precisely as if upon a copy of <code>s1</code> extended _in length
         *      to just include the indicated interval
         * </UL>
         * @param s1 the <code>String</code> into which to insert <code>s2</code>
         * @param start the position, with origin one, at which to start the insertion
         * @param length the number of characters _in <code>s1</code> to replace
         * @param s2 the <code>String</code> to insert into <code>s1</code>
         * @return <code>s2</code> inserted into <code>s1</code>, as indicated
         *      by <code>start</code> and <code>length</code> and adjusted for
         *      boundry conditions
         */
        public static string insert(string s1, int start, int length, string s2)
        {

            if (s1 == null)
            {
                return s2;
            }

            if (s2 == null)
            {
                return s1;
            }

            int len1 = s1.Length;
            int len2 = s2.Length;

            start--;

            if (start < 0 || length <= 0 || len2 == 0 || start > len1)
            {
                return s1;
            }

            if (start + length > len1)
            {
                length = len1 - start;
            }

            return s1.Substring(0, start - (0)) + s2 + s1.Substring(start + length);
        }

        /**
         * Returns a copy of the given <code>String</code>, with all upper case
         * characters converted to lower case. This uses the default Java String
         * conversion.
         * @param s the <code>String</code> from which to produce a lower case
         *      version
         * @return a lower case version of <code>s</code>
         */
        public static string lcase(string s)
        {
            return (s == null) ? null
                               : s.ToLower();
        }

        /**
         * Returns the leftmost <code>count</code> characters from the given
         * <code>String</code>. <p>
         *
         * <b>Note:</b> boundry conditions are handled _in the following order of
         * precedence:
         *
         * <UL>
         *  <LI> if <code>s</code> is <code>null</code>, then <code>null</code>
         *      is returned
         *  <LI> if <code>count</code> is less than 1, then a zero-length
         *       <code>String</code> is returned
         *  <LI> if <code>count</code> is greater than the length of <code>s</code>,
         *      then a copy of <code>s</code> is returned
         * </UL>
         * @param s the <code>String</code> from which to retrieve the leftmost
         *      characters
         * @param count the count of leftmost characters to retrieve
         * @return the leftmost <code>count</code> characters of <code>s</code>
         */
        public static string left(string s, int count)
        {

            if (s == null)
            {
                return null;
            }

            return s.Substring(0, ((count < 0) ? 0
                                               : (count < s.Length) ? count
                                                                      : s.Length));
        }

        // fredt@users - 20020819 - patch 595854 by thomasm@users

        /**
         * Returns the number of characters _in the given <code>String</code>.
         * This includes trailing blanks.
         *
         * @param s the <code>String</code> for which to determine length
         * @return the length of <code>s</code>, including trailing blanks
         */
        public static int length(string s)
        {
            return s == null ? 0
                             : (s.Length);
        }

        /**
         * Returns the number of bytes _in the given <code>String</code>.
         * This includes trailing blanks.
         *
         * @param s the <code>String</code> for which to determine the octet length
         * @return the octet length of <code>s</code>, including trailing blanks
         * @since 1.7.2
         */
        public static int? octetLength(string s)
        {
            return s == null ? 0
                             : (s.Length * 2);
        }

        /**
         * Returns the number of bits _in the given <code>String</code>.
         * This includes trailing blanks.
         *
         * @param s the <code>String</code> for which to determine the bit length
         * @return the bit length of <code>s</code>, including trailing blanks
         * @since 1.7.2
         */
        public static int bitLength(string s)
        {
            return s == null ? 0
                             : (s.Length * 16);
        }

        /**
         * Returns the starting position of the first occurrence of
         * the given <code>search</code> <code>String</code> object within
         * the given <code>String</code> object, <code>s</code>.
         *
         * The search for the first occurrence of <code>search</code> begins with
         * the first character position _in <code>s</code>, unless the optional
         * argument, <code>start</code>, is specified (non-null). If
         * <code>start</code> is specified, the search begins with the character
         * position indicated by the value of <code>start</code>, where the
         * first character position _in <code>s</code> is indicated by the value 1.
         * If <code>search</code> is not found within <code>s</code>, the
         * value 0 is returned.
         * @param search the <code>String</code> occurence to find _in <code>s</code>
         * @param s the <code>String</code> within which to find the first
         *      occurence of <code>search</code>
         * @param start the optional character position from which to start
         *      looking _in <code>s</code>
         * @return the one-based starting position of the first occurrence of
         *      <code>search</code> within <code>s</code>, or 0 if not found
         */
        public static int locate(string search, string s, int start)
        {

            if (s == null || search == null)
            {
                return 0;
            }

            int i = (start == 0) ? 0
                                    : start - 1;

            return s.IndexOf(search, (i < 0) ? 0
                                             : i) + 1;
        }

        /**
         * As locate but from start position l. <p>
         *
         * @param search the <code>String</code> occurence to find _in <code>s</code>
         * @param s the <code>String</code> within which to find the first
         *      occurence of <code>search</code>
         * @return the one-based starting position of the first occurrence of
         *      <code>search</code> within <code>s</code>, or 0 if not found
         */
        public static int position(string search, string s)
        {
            return locate(search, s, 0);
        }

        /**
         * Returns the characters of the given <code>String</code>, with the
         * leading spaces removed. Characters such as TAB are not removed.
         *
         * @param s the <code>String</code> from which to remove the leading blanks
         * @return the characters of the given <code>String</code>, with the leading
         *      spaces removed
         */
        public static string ltrim(string s)
        {

            if (s == null)
            {
                return s;
            }

            int len = s.Length,
                i = 0;

            while (i < len && s[(i)] <= ' ')
            {
                i++;
            }

            return (i == 0) ? s
                            : s.Substring(i);
        }

        /**
         * Converts a raw binary value, as represented by the given
         * <code>String</code>, to the equivalent <code>String</code>
         * of hexidecimal digit characters. <p>
         *
         * This conversion has the effect of expanding the character count 1:4.
         *
         * @param s the raw binary value, as a <code>String</code>
         * @return an equivalent <code>String</code> of hexidecimal digit characters
         */
        public static string rawToHex(string s)
        {

            if (s == null)
            {
                return null;
            }

            char[] from = s.ToCharArray();
            string hex;
            StringBuilder to = new StringBuilder(4 * s.Length);

            for (int i = 0; i < from.Length; i++)
            {
                hex = String.Format("{0:X}", (int)(from[i] & 0xffff));

                for (int j = hex.Length; j < 4; j++)
                {
                    to.Append('0');
                }

                to.Append(hex);
            }

            return (to.ToString());
        }

        /**
         * Returns a <code>String</code> composed of the given <code>String</code>,
         * repeated  <code>count</code> times.
         *
         * @param s the <code>String</code> to repeat
         * @param count the number of repetitions
         * @return the given <code>String</code>, repeated <code>count</code> times
         */
        public static string repeat(string s, int count)
        {

            if (s == null || count < 0)
            {
                return null;
            }

            int i = count;
            StringBuilder b = new StringBuilder(s.Length * i);

            while (i-- > 0)
            {
                b.Append(s);
            }

            return b.ToString();
        }

        // fredt@users - 20020903 - patch 1.7.1 - bug fix to allow multiple replaces

        /**
         * Replaces all occurrences of <code>replace</code> _in <code>s</code>
         * with the <code>String</code> object: <code>with</code>
         * @param s the target for replacement
         * @param replace the substring(s), if any, _in <code>s</code> to replace
         * @param with the value to substitute for <code>replace</code>
         * @return <code>s</code>, with all occurences of <code>replace</code>
         *      replaced by <code>with</code>
         */
        public static string replace(string s, string replace, string with)
        {

            if (s == null || replace == null)
            {
                return s;
            }

            if (with == null)
            {
                with = "";
            }

            StringBuilder b = new StringBuilder();
            int start = 0;
            int lenreplace = replace.Length;

            while (true)
            {
                int i = s.IndexOf(replace, start);

                if (i == -1)
                {
                    b.Append(s.Substring(start));

                    break;
                }

                b.Append(s.Substring(start, i - (start)));
                b.Append(with);

                start = i + lenreplace;
            }

            return b.ToString();
        }

        /**
         * Returns the rightmost <code>count</code> characters of the given
         * <code>String</code>, <code>s</code>. <p>
         *
         * <b>Note:</b> boundry conditions are handled _in the following order of
         * precedence: <p>
         *
         * <UL>
         *  <LI> if <code>s</code> is <code>null</code>, <code>null</code> is returned
         *  <LI> if <code>count</code> is less than one, a zero-length
         *      <code>String</code> is returned
         *  <LI> if <code>count</code> is greater than the length of <code>s</code>,
         *      a copy of <code>s</code> is returned
         * </UL>
         * @param s the <code>String</code> from which to retrieve the rightmost
         *      <code>count</code> characters
         * @param count the number of rightmost characters to retrieve
         * @return the rightmost <code>count</code> characters of <code>s</code>
         */
        public static string right(string s, int count)
        {

            if (s == null)
            {
                return null;
            }

            count = s.Length - count;

            return s.Substring((count < 0) ? 0
                                           : (count < s.Length) ? count
                                                                  : s.Length);
        }

        // fredt@users 20020530 - patch 1.7.0 fredt - trim only the space character

        /**
         * Returns the characters of the given <code>String</code>, with trailing
         * spaces removed.
         * @param s the <code>String</code> from which to remove the trailing blanks
         * @return the characters of the given <code>String</code>, with the
         * trailing spaces removed
         */
        public static string rtrim(string s)
        {

            if (s == null)
            {
                return s;
            }

            int endindex = s.Length - 1;
            int i = endindex;

            for (; i >= 0 && s[(i)] == ' '; i--) { }

            return i == endindex ? s
                                 : s.Substring(0, i + 1 - (0));
        }

        /**
         * Returns the character sequence <code>s</code>, with the leading,
         * trailing or both the leading and trailing occurences of the first
         * character of the character sequence <code>trimstr</code> removed. <p>
         *
         * This method is _in support of the standard SQL string function TRIM.
         * Ordinarily, the functionality of this method is accessed from SQL using
         * the following syntax: <p>
         *
         * <pre class="SqlCodeExample">
         * &lt;trim function&gt; ::= TRIM &lt;left paren&gt; &lt;trim operands&gt; &lt;right paren&gt;
         * &lt;trim operands&gt; ::= [ [ &lt;trim specification&gt; ] [ &lt;trim character&gt; ] FROM ] &lt;trim source&gt;
         * &lt;trim source&gt; ::= &lt;character value expression&gt;
         * &lt;trim specification&gt; ::= LEADING | TRAILING | BOTH
         * &lt;trim character&gt; ::= &lt;character value expression&gt;
         * </pre>
         *
         * @param s the string to trim
         * @param trimstr the character whose occurences will be removed
         * @param leading if true, remove leading occurences
         * @param trailing if true, remove trailing occurences
         * @return s, with the leading, trailing or both the leading and trailing
         *      occurences of the first character of <code>trimstr</code> removed
         * @since 1.7.2
         */
        public static string trim(string s, string trimstr, bool leading,
                                  bool trailing)
        {

            if (s == null)
            {
                return s;
            }

            int trim = trimstr[(0)];
            int endindex = s.Length;

            if (trailing)
            {
                for (--endindex; endindex >= 0 && s[(endindex)] == trim;
                        endindex--) { }

                endindex++;
            }

            if (endindex == 0)
            {
                return "";
            }

            int startindex = 0;

            if (leading)
            {
                while (startindex < endindex && s[(startindex)] == trim)
                {
                    startindex++;
                }
            }

            if (startindex == 0 && endindex == s.Length)
            {
                return s;
            }
            else
            {
                return s.Substring(startindex, endindex - (startindex));
            }
        }

        // fredt@users 20011010 - patch 460907 by fredt - soundex

        /**
         * Returns a four character code representing the sound of the given
         * <code>String</code>. Non-ASCCI characters _in the
         * input <code>String</code> are ignored. <p>
         *
         * This method was
         * rewritten for HSQLDB by fredt@users to comply with the description at
         * <a href="http://www.nara.gov/genealogy/coding.html">
         * http://www.nara.gov/genealogy/coding.html</a>.<p>
         * @param s the <code>String</code> for which to calculate the 4 character
         *      <code>SOUNDEX</code> value
         * @return the 4 character <code>SOUNDEX</code> value for the given
         *      <code>String</code>
         */
        public static string soundex(string s)
        {

            if (s == null)
            {
                return s;
            }

            s = s.ToUpper(System.Globalization.CultureInfo.CreateSpecificCulture("en-US"));

            int len = s.Length;
            char[] b = new char[] {
            '0', '0', '0', '0'
        };
            char lastdigit = '0';

            for (int i = 0, j = 0; i < len && j < 4; i++)
            {
                char c = s[(i)];
                char newdigit;

                if ("AEIOUY".IndexOf(c) != -1)
                {
                    newdigit = '7';
                }
                else if (c == 'H' || c == 'W')
                {
                    newdigit = '8';
                }
                else if ("BFPV".IndexOf(c) != -1)
                {
                    newdigit = '1';
                }
                else if ("CGJKQSXZ".IndexOf(c) != -1)
                {
                    newdigit = '2';
                }
                else if (c == 'D' || c == 'T')
                {
                    newdigit = '3';
                }
                else if (c == 'L')
                {
                    newdigit = '4';
                }
                else if (c == 'M' || c == 'N')
                {
                    newdigit = '5';
                }
                else if (c == 'R')
                {
                    newdigit = '6';
                }
                else
                {
                    continue;
                }

                if (j == 0)
                {
                    b[j++] = c;
                    lastdigit = newdigit;
                }
                else if (newdigit <= '6')
                {
                    if (newdigit != lastdigit)
                    {
                        b[j++] = newdigit;
                        lastdigit = newdigit;
                    }
                }
                else if (newdigit == '7')
                {
                    lastdigit = newdigit;
                }
            }

            return new String(b, 0, 4);
        }

        /**
         * Returns a <code>String</code> consisting of <code>count</code> spaces, or
         * <code>null</code> if <code>count</code> is less than zero. <p>
         *
         * @param count the number of spaces to produce
         * @return a <code>String</code> of <code>count</code> spaces
         */
        public static string space(int count)
        {

            if (count < 0)
            {
                return null;
            }

            char[] c = new char[count];

            while (count > 0)
            {
                c[--count] = ' ';
            }

            return new String(c);
        }

        /**
         * Returns the characters from the given <code>String</code>, starting at
         * the indicated one-based <code>start</code> position and extending the
         * (optional) indicated <code>length</code>. If <code>length</code> is not
         * specified (is <code>null</code>), the remainder of <code>s</code> is
         * implied.
         *
         * The rules for boundary conditions on s, start and length are,
         * _in order of precedence: <p>
         *
         * 1.) if s is null, return null
         *
         * 2.) If length is less than 1, return null.
         *
         * 3.) If start is 0, it is treated as 1.
         *
         * 4.) If start is positive, count from the beginning of s to find
         *     the first character postion.
         *
         * 5.) If start is negative, count backwards from the end of s
         *     to find the first character.
         *
         * 6.) If, after applying 2.) or 3.), the start position lies outside s,
         *     then return null
         *
         * 7.) if length is ommited or is greated than the number of characters
         *     from the start position to the end of s, return the remaineder of s,
         *     starting with the start position.
         *
         * @param s the <code>String</code> from which to produce the indicated
         *      substring
         * @param start the starting position of the desired substring
         * @param length the length of the desired substring
         * @return the indicted substring of <code>s</code>.
         */

        // fredt@users 20020210 - patch 500767 by adjbirch@users - modified
        // boucherb@users 20050205 - patch to correct bug 1107477
        public static string substring(string s, int start,
                                        int length)
        {

            if (s == null)
            {
                return null;
            }

            int sl = s.Length;
            int ol = (length == -1) ? sl
                                      : length;

            if (ol < 1)
            {
                return null;
            }

            if (start < 0)
            {
                start = sl + start;
            }
            else if (start > 0)
            {
                start--;
            }

            if (start < 0 || start >= sl)
            {
                return null;
            }
            else if (start > sl - ol)
            {
                ol = sl - start;
            }

            return s.Substring(start, start + ol - (start));
        }

        /**
         * Returns a copy of the given <code>String</code>, with all lower case
         * characters converted to upper case using the default Java method.
         * @param s the <code>String</code> from which to produce an upper case
         *      version
         * @return an upper case version of <code>s</code>
         */
        public static string ucase(string s)
        {
            return (s == null) ? null
                               : s.ToUpper();
        }

        // TIME AND DATE

        /**
         * Returns the current date as a date value. <p>
         *
         * Dummy mehtod.<p>
         *
         * @return a date value representing the current date
         */
        public static DateTime curdate(IDbConnection c)
        {
            return DateTime.MinValue;
        }

        /**
         * Returns the current local time as a time value. <p>
         *
         * Dummy mehtod.<p>
         *
         * @return a time value representing the current local time
         */
        public static DateTime curtime(IDbConnection c)
        {
            return DateTime.MinValue;
        }

        /**
         * Returns a character string containing the name of the day
         * (Sunday, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday )
         * for the day portion of the given <code>java.sql.Date</code>.
         * @param d the date value from which to extract the day name
         * @return the name of the day corresponding to the given
         * <code>java.sql.Date</code>
         */
        public static string dayname(DateTime d)
        {

            if (d == null)
            {
                return null;
            }

            return d.ToString("dddd");
        }

        /**
         * Returns the day of the month from the given date value, as an integer
         * value _in the range of 1-31.
         *
         * @param d the date value from which to extract the day of month
         * @return the day of the month from the given date value
         */
        public static int dayofmonth(DateTime d)
        {

            if (d == null)
            {
                return int.MinValue;
            }

            return d.Day;
        }

        /**
         * Returns the day of the week from the given date value, as an integer
         * value _in the range 1-7, where 1 represents Sunday.
         *
         * @param d the date value from which to extract the day of week
         * @return the day of the week from the given date value
         */
        public static int dayofweek(DateTime d)
        {

            if (d == null)
            {
                return int.MinValue;
            }

            return (int)d.DayOfWeek;
        }

        /**
         * Returns the day of the year from the given date value, as an integer
         * value _in the range 1-366.
         *
         * @param d the date value from which to extract the day of year
         * @return the day of the year from the given date value
         */
        public static int dayofyear(DateTime d)
        {

            if (d == null)
            {
                return int.MinValue;
            }

            return d.DayOfYear;
        }

        /**
         * Returns the hour from the given time value, as an integer value in
         * the range of 0-23.
         *
         * @param t the time value from which to extract the hour of day
         * @return the hour of day from the given time value
         */

        // fredt@users 20020210 - patch 513005 by sqlbob@users (RMP) - hour
        public static int hour(DateTime t)
        {

            if (t == null)
            {
                return int.MinValue;
            }

            return t.Hour;
        }

        /**
         * Returns the minute from the given time value, as integer value in
         * the range of 0-59.
         *
         * @param t the time value from which to extract the minute value
         * @return the minute value from the given time value
         */
        public static int minute(DateTime t)
        {

            if (t == null)
            {
                return int.MinValue;
            }

            return t.Minute;
        }

        /**
         * Returns the month from the given date value, as an integer value _in the
         * range of 1-12. <p>
         *
         * The sql_month database property is now obsolete.
         * The function always returns the SQL (1-12) value for month.
         *
         * @param d the date value from which to extract the month value
         * @return the month value from the given date value
         */
        public static int month(DateTime d)
        {

            if (d == null)
            {
                return int.MinValue;
            }

            return d.Month;
        }

        /**
         * Returns a character string containing the name of month
         * (January, February, March, April, May, June, July, August,
         * September, October, November, December) for the month portion of
         * the given date value.
         *
         * @param d the date value from which to extract the month name
         * @return a string representing the month name from the given date value
         */
        public static string monthname(DateTime d)
        {

            if (d == null)
            {
                return null;
            }

            return System.Globalization.CultureInfo.CurrentCulture.DateTimeFormat.MonthNames[d.Month];
        }

        /**
         * Returns the current date and time as a timestamp value. <p>
         *
         * Dummy mehtod.<p>
         *
         * @return a timestamp value representing the current date and time
         */
        public static DateTime now(IDbConnection c)
        {
            return DateTime.MinValue;
        }

        /**
         * Returns the quarter of the year _in the given date value, as an integer
         * value _in the range of 1-4. <p>
         *
         * @param d the date value from which to extract the quarter of the year
         * @return an integer representing the quater of the year from the given
         *      date value
         */
        public static int quarter(DateTime d)
        {

            if (d == null)
            {
                return int.MinValue;
            }

            return ((int)d.Month / 3) + 1;
        }

        /**
         * Returns the second of the given time value, as an integer value in
         * the range of 0-59.
         *
         * @param d the date value from which to extract the second of the hour
         * @return an integer representing the second of the hour from the
         *      given time value
         */
        public static int second(DateTime d)
        {

            if (d == null)
            {
                return int.MinValue;
            }

            return d.Second;
        }

        /**
         * Returns the week of the year from the given date value, as an integer
         * value _in the range of 1-53. <p>
         *
         * @param d the date value from which to extract the week of the year
         * @return an integer representing the week of the year from the given
         *      date value
         */
        public static int week(DateTime d)
        {

            if (d == null)
            {
                return int.MinValue;
            }

            return System.Globalization.CultureInfo.CurrentCulture.Calendar.GetDayOfYear(d);
        }

        /**
         * Returns the year from the given date value, as an integer value in
         * the range of 1-9999. <p>
         *
         * @param d the date value from which to extract the year
         * @return an integer value representing the year from the given
         *      date value
         */
        public static int year(DateTime d)
        {

            if (d == null)
            {
                return int.MinValue;
            }

            return d.Year;
        }

        /**
         * @since 1.8.0
         */
        public static string to_char(DateTime d, string format)
        {

            if (d == null || format == null)
            {
                return null;
            }

            return d.ToString(format);
        }

        // date calculations.

        /**
         * Returns the number of units elapsed between two dates.<p>
         * The datapart parameter indicates the part to be used for computing the
         * difference. Supported types include: 'year', 'yy', 'month', 'mm'
         * 'day', 'dd', 'hour', 'hh', 'minute', 'mi', 'second', 'ss', 'millisecond',
         * 'ms'.
         *
         * Contributed by Michael Landon<p>
         *
         * @param datepart Specifies the unit _in which the interval is to be measured.
         * @param d1 The starting datetime value for the interval. This value is
         *           subtracted from d2 to return the number of
         *           date-parts between the two arguments.
         * @param d2 The ending datetime for the interval. d1 is subtracted
         *           from this value to return the number of date-parts
         *           between the two arguments.
         *
         * since 1.7.3  
         */
        public static long datediff(string datepart, DateTime d1,
                                    DateTime d2)
        {

            // make sure we've got valid data
            if (d1 == null || d2 == null)
            {
                return long.MinValue;
            }

            TimeSpan span = d2 - d1;

            //if ("yy".Equals(datepart,StringComparison.InvariantCultureIgnoreCase)
            //        || "year".Equals(datepart, StringComparison.InvariantCultureIgnoreCase))
            //{

            //    return  span.ye;
            //}
            //else if ("mm".Equals(datepart, StringComparison.InvariantCultureIgnoreCase)
            //           || "month".Equals(datepart, StringComparison.InvariantCultureIgnoreCase))
            //{
            //    return (getElapsed(Calendar.MONTH, d1, d2));
            //}
            if ("dd".Equals(datepart, StringComparison.InvariantCultureIgnoreCase)
                       || "day".Equals(datepart, StringComparison.InvariantCultureIgnoreCase))
            {
                return (long)span.TotalDays;
            }
            else if ("hh".Equals(datepart, StringComparison.InvariantCultureIgnoreCase)
                       || "hour".Equals(datepart, StringComparison.InvariantCultureIgnoreCase))
            {
                return (long)span.TotalHours;
            }
            else if ("mi".Equals(datepart, StringComparison.InvariantCultureIgnoreCase)
                       || "minute".Equals(datepart, StringComparison.InvariantCultureIgnoreCase))
            {
                return (long)span.TotalMinutes;
            }
            else if ("ss".Equals(datepart, StringComparison.InvariantCultureIgnoreCase)
                       || "second".Equals(datepart, StringComparison.InvariantCultureIgnoreCase))
            {
                return (long)span.TotalSeconds;
            }
            else if ("ms".Equals(datepart, StringComparison.InvariantCultureIgnoreCase)
                       || "millisecond".Equals(datepart, StringComparison.InvariantCultureIgnoreCase))
            {
                return (long)span.TotalMilliseconds;
            }
            else
            {
                throw Trace.error(Trace.INVALID_CONVERSION);
            }
        }

        ///**
        // * Private method used to do actual calculation units elapsed between
        // * two given dates. <p>
        // *
        // * @param field Calendar field to use to calculate elapsed time
        // * @param d1 The starting date for the interval. This value is
        // *           subtracted from d2 to return the number of
        // *           date-parts between the two arguments.
        // * @param d2 The ending date for the interval. d1 is subtracted
        // *           from this value to return the number of date-parts
        // *           between the two arguments.
        // */
        //private static long getElapsed(int field, DateTime d1,
        //                               DateTime d2) {

        //    // can we do this very simply?
        //    if (field == Calendar.MILLISECOND) {
        //        return d2  - d1 ;
        //    }

        //    // ok, let's work a little harder:
        //    Calendar g1 = Calendar.getInstance(),
        //             g2 = Calendar.getInstance();

        //    g1.setTime(d1);
        //    g2.setTime(d2);
        //    g1.set(Calendar.MILLISECOND, 0);
        //    g2.set(Calendar.MILLISECOND, 0);

        //    if (field == Calendar.SECOND) {
        //        return (g2   - g1  ) / 1000;
        //    }

        //    g1.set(Calendar.SECOND, 0);
        //    g2.set(Calendar.SECOND, 0);

        //    if (field == Calendar.MINUTE) {
        //        return (g2   - g1  )
        //               / (1000 * 60);
        //    }

        //    g1.set(Calendar.MINUTE, 0);
        //    g2.set(Calendar.MINUTE, 0);

        //    if (field == Calendar.HOUR) {
        //        return (g2   - g1  )
        //               / (1000 * 60 * 60);
        //    }    // end if-else

        //    // if we got here, then we really need to work:
        //    long  elapsed = 0;
        //    short sign    = 1;

        //    if (g2.before(g1)) {
        //        sign = -1;

        //        Calendar tmp = g1;

        //        g1 = g2;
        //        g2 = tmp;
        //    }    // end if

        //    g1.set(Calendar.HOUR_OF_DAY, 0);
        //    g2.set(Calendar.HOUR_OF_DAY, 0);

        //    if (field == Calendar.MONTH || field == Calendar.YEAR) {
        //        g1.set(Calendar.DATE, 1);
        //        g2.set(Calendar.DATE, 1);
        //    }

        //    if (field == Calendar.YEAR) {
        //        g1.set(Calendar.MONTH, 1);
        //        g2.set(Calendar.MONTH, 1);
        //    }    // end if-else

        //    // then calculate elapsed units
        //    while (g1.before(g2)) {
        //        g1.add(field, 1);

        //        elapsed++;
        //    }

        //    return sign * elapsed;
        //}    // end getElapsed

        // SYSTEM
        /*
         * All system functions that return Session dependent information are
         * dummies here.
         */

        /**
         * Returns the name of the database corresponding to this connection.
         *
         * @param conn the connection for which to retrieve the database name
         * @return the name of the database for the given connection
         * @  if a database access error occurs
         */
        public static string database(IDbConnection conn)
        {
            return null;
        }

        /**
         * Returns the user's authorization name (the user's name as known to this
         * database).
         *
         * @param conn the connection for which to retrieve the user name
         * @return the user's name as known to the database
         * @  if a database access error occurs
         */
        public static string user(IDbConnection conn)
        {
            return null;
        }

        /**
         * Retrieves the last auto-generated integer indentity value
         * used by this connection. <p>
         *
         * Dummy mehtod.<p>
         *
         * @return the connection's the last generated integer identity value
         * @  if a database access error occurs
         */
        public static int identity()
        {
            return 0;
        }

        // JDBC SYSTEM

        /**
         * Retrieves the autocommit status of this connection. <p>
         *
         * @param conn the <code>Connection</code> object for which to retrieve
         *      the current autocommit status
         * @return a bool value representing the connection's autocommit status
         * @since 1.7.0
         */
        public static bool getAutoCommit(IDbConnection conn)
        {
            return false;
        }

        /**
         * Retrieves the full version number of this database product. <p>
         *
         * @return database version number as a <code>String</code> object
         * @since 1.8.0.4
         */
        public static string getDatabaseFullProductVersion()
        {
            return HsqlDatabaseProperties.THIS_FULL_VERSION;
        }

        /**
         * Retrieves the name of this database product. <p>
         *
         * @return database product name as a <code>String</code> object
         * @since 1.7.2
         */
        public static string getDatabaseProductName()
        {
            return HsqlDatabaseProperties.PRODUCT_NAME;
        }

        /**
         * Retrieves the version number of this database product. <p>
         *
         * @return database version number as a <code>String</code> object
         * @since 1.7.2
         */
        public static string getDatabaseProductVersion()
        {
            return HsqlDatabaseProperties.THIS_VERSION;
        }

        /**
         * Retrieves the major version number of this database. <p>
         *
         * @return the database's major version as an <code>int</code> value
         * @since 1.7.2
         */
        public static int getDatabaseMajorVersion()
        {
            return HsqlDatabaseProperties.MAJOR;
        }

        /**
         * Retrieves the major version number of this database. <p>
         *
         * @return the database's major version as an <code>int</code> value
         * @since 1.7.2
         */
        public static int getDatabaseMinorVersion()
        {
            return HsqlDatabaseProperties.MINOR;
        }

        /**
         * Retrieves whether this connection is _in read-only mode. <p>
         *
         * Dummy mehtod.<p>
         *
         * @param conn the <code>Connection</code> object for which to retrieve
         *      the current read-only status
         * @return  <code>true</code> if connection is read-only and
         *      <code>false</code> otherwise
         * @since 1.7.2
         */
        public static bool isReadOnlyConnection(IDbConnection conn)
        {
            return false;
        }

        /**
         * Dummy method. Retrieves whether this database is _in read-only mode. <p>
         *
         * @param c the <code>Connection</code> object for which to retrieve
         *      the current database read-only status
         * @return <code>true</code> if so; <code>false</code> otherwise
         * @since 1.7.2
         */
        public static bool isReadOnlyDatabase(IDbConnection c)
        {
            return false;
        }

        /**
         * Retrieves whether the files of this database are _in read-only mode. <p>
         *
         * Dummy mehtod.<p>
         *
         * @param c the <code>Connection</code> object for which to retrieve
         *      the current database files read-only status
         * @return <code>true</code> if so; <code>false</code> otherwise
         * @since 1.7.2
         */
        public static bool isReadOnlyDatabaseFiles(IDbConnection c)
        {
            return false;
        }

        public const int _abs = 0;
        public const int _ascii = 1;
        public const int _bitand = 2;
        public const int _bitLength = 3;
        public const int _bitor = 4;
        public const int _bitxor = 5;
        public const int _character = 6;
        public const int _concat = 7;
        public const int _cot = 8;
        public const int _curdate = 9;
        public const int _curtime = 10;
        public const int _database = 11;
        public const int _datediff = 12;
        public const int _day = 13;
        public const int _dayname = 14;
        public const int _dayofmonth = 15;
        public const int _dayofweek = 16;
        public const int _dayofyear = 17;
        public const int _difference = 18;
        public const int _getAutoCommit = 19;
        public const int _getDatabaseFullProductVersion = 20;
        public const int _getDatabaseMajorVersion = 21;
        public const int _getDatabaseMinorVersion = 22;
        public const int _getDatabaseProductName = 23;
        public const int _getDatabaseProductVersion = 24;
        public const int _hexToRaw = 25;
        public const int _hour = 26;
        public const int _identity = 27;
        public const int _insert = 28;
        public const int _isReadOnlyConnection = 29;
        public const int _isReadOnlyDatabase = 30;
        public const int _isReadOnlyDatabaseFiles = 31;
        public const int _lcase = 32;
        public const int _left = 33;
        public const int _length = 34;
        public const int _locate = 35;
        public const int _log10 = 36;
        public const int _ltrim = 37;
        public const int _minute = 38;
        public const int _mod = 39;
        public const int _month = 40;
        public const int _monthname = 41;
        public const int _now = 42;
        public const int _octetLength = 43;
        public const int _pi = 44;
        public const int _position = 45;
        public const int _quarter = 46;
        public const int _rand = 47;
        public const int _rawToHex = 48;
        public const int _repeat = 49;
        public const int _replace = 50;
        public const int _right = 51;
        public const int _round = 52;
        public const int _roundMagic = 53;
        public const int _rtrim = 54;
        public const int _second = 55;
        public const int _sign = 56;
        public const int _soundex = 57;
        public const int _space = 58;
        public const int _substring = 59;
        public const int _to_char = 60;
        public const int _trim = 61;
        public const int _truncate = 62;
        public const int _ucase = 63;
        public const int _user = 64;
        public const int _week = 65;
        public const int _year = 66;

        /** @todo  see bitxor and datediff numbering */

        //
        private static Dictionary<string, int> functionMap =
            new Dictionary<string, int>(67, StringComparer.InvariantCultureIgnoreCase);
        static double piValue = Library.pi();

        static Library()
        {
            functionMap.Add("abs", _abs);
            functionMap.Add("ascii", _ascii);
            functionMap.Add("bitand", _bitand);
            functionMap.Add("bitlength", _bitLength);
            functionMap.Add("bitor", _bitor);
            functionMap.Add("bitxor", _bitor);
            functionMap.Add("character", _character);
            functionMap.Add("concat", _concat);
            functionMap.Add("cot", _cot);
            functionMap.Add("curdate", _curdate);
            functionMap.Add("curtime", _curtime);
            functionMap.Add("database", _database);
            functionMap.Add("datediff", _datediff);
            functionMap.Add("dayname", _dayname);
            functionMap.Add("day", _day);
            functionMap.Add("dayofmonth", _dayofmonth);
            functionMap.Add("dayofweek", _dayofweek);
            functionMap.Add("dayofyear", _dayofyear);
            functionMap.Add("difference", _difference);
            functionMap.Add("getAutoCommit", _getAutoCommit);
            functionMap.Add("getDatabaseFullProductVersion",
                            _getDatabaseFullProductVersion);
            functionMap.Add("getDatabaseMajorVersion", _getDatabaseMajorVersion);
            functionMap.Add("getDatabaseMinorVersion", _getDatabaseMinorVersion);
            functionMap.Add("getDatabaseProductName", _getDatabaseProductName);
            functionMap.Add("getDatabaseProductVersion",
                            _getDatabaseProductVersion);
            functionMap.Add("hexToRaw", _hexToRaw);
            functionMap.Add("hour", _hour);
            functionMap.Add("identity", _identity);
            functionMap.Add("insert", _insert);
            functionMap.Add("isReadOnlyConnection", _isReadOnlyConnection);
            functionMap.Add("isReadOnlyDatabase", _isReadOnlyDatabase);
            functionMap.Add("isReadOnlyDatabaseFiles", _isReadOnlyDatabaseFiles);
            functionMap.Add("lcase", _lcase);
            functionMap.Add("left", _left);
            functionMap.Add("length", _length);
            functionMap.Add("locate", _locate);
            functionMap.Add("log10", _log10);
            functionMap.Add("ltrim", _ltrim);
            functionMap.Add("minute", _minute);
            functionMap.Add("mod", _mod);
            functionMap.Add("month", _month);
            functionMap.Add("monthname", _monthname);
            functionMap.Add("now", _now);
            functionMap.Add("octetLength", _octetLength);
            functionMap.Add("pi", _pi);
            functionMap.Add("position", _position);
            functionMap.Add("quarter", _quarter);
            functionMap.Add("rand", _rand);
            functionMap.Add("rawToHex", _rawToHex);
            functionMap.Add("repeat", _repeat);
            functionMap.Add("replace", _replace);
            functionMap.Add("right", _right);
            functionMap.Add("round", _round);
            functionMap.Add("roundMagic", _roundMagic);
            functionMap.Add("rtrim", _rtrim);
            functionMap.Add("second", _second);
            functionMap.Add("sign", _sign);
            functionMap.Add("soundex", _soundex);
            functionMap.Add("space", _space);
            functionMap.Add("substring", _substring);
            functionMap.Add("to_char", _to_char);
            functionMap.Add("trim", _trim);
            functionMap.Add("truncate", _truncate);
            functionMap.Add("ucase", _ucase);
            functionMap.Add("user", _user);
            functionMap.Add("week", _week);
            functionMap.Add("year", _year);
        }

        public static Object invoke(int fID, Object[] _params)
        {

            try
            {
                switch (fID)
                {

                    case _abs:
                        {
                            return
                                Library.abs(((double)_params[0]));
                        }
                    case _ascii:
                        {
                            return ascii((String)_params[0]);
                        }
                    case _bitand:
                        {
                            return (
                                bitand(((int)_params[0]),
                                       ((int)_params[1])));
                        }
                    case _bitLength:
                        {
                            return bitLength((String)_params[0]);
                        }
                    case _bitor:
                        {
                            return (
                                bitor(((int)_params[0]),
                                      ((int)_params[1])));
                        }
                    case _bitxor:
                        {
                            return (
                                bitxor(((int)_params[0]),
                                       ((int)_params[1])));
                        }
                    case _character:
                        {
                            return character(((int)_params[0]));
                        }
                    case _concat:
                        {
                            return concat((String)_params[0], (String)_params[1]);
                        }
                    case _cot:
                        {
                            return (
                                cot(((double)_params[0])));
                        }
                    case _curdate:
                        {
                            return null;
                        }
                    case _curtime:
                        {
                            return null;
                        }
                    case _database:
                        {
                            return null;
                        }
                    case _datediff:
                        {
                            return datediff((String)_params[0],
                                            (DateTime)_params[1],
                                            (DateTime)_params[2]);
                        }
                    case _dayname:
                        {
                            return dayname((DateTime)_params[0]);
                        }
                    case _dayofmonth:
                    case _day:
                        {
                            return dayofmonth((DateTime)_params[0]);
                        }
                    case _dayofweek:
                        {
                            return dayofweek((DateTime)_params[0]);
                        }
                    case _dayofyear:
                        {
                            return dayofyear((DateTime)_params[0]);
                        }
                    case _difference:
                        {
                            return (difference((String)_params[0],
                                                               (String)_params[1]));
                        }
                    case _getAutoCommit:
                        {
                            return null;
                        }
                    case _getDatabaseFullProductVersion:
                        {
                            return getDatabaseFullProductVersion();
                        }
                    case _getDatabaseMajorVersion:
                        {
                            return (getDatabaseMajorVersion());
                        }
                    case _getDatabaseMinorVersion:
                        {
                            return (getDatabaseMinorVersion());
                        }
                    case _getDatabaseProductName:
                        {
                            return getDatabaseProductName();
                        }
                    case _getDatabaseProductVersion:
                        {
                            return getDatabaseProductVersion();
                        }
                    case _hexToRaw:
                        {
                            return hexToRaw((String)_params[0]);
                        }
                    case _hour:
                        {
                            return hour((DateTime)_params[0]);
                        }
                    case _identity:
                        {
                            return null;
                        }
                    case _insert:
                        {
                            return insert((String)_params[0],
                                          ((int)_params[1]),
                                          ((int)_params[2]),
                                          (String)_params[3]);
                        }
                    case _isReadOnlyConnection:
                        {
                            return null;
                        }
                    case _isReadOnlyDatabase:
                        {
                            return null;
                        }
                    case _lcase:
                        {
                            return lcase((String)_params[0]);
                        }
                    case _left:
                        {
                            return left((String)_params[0],
                                        ((int)_params[1]));
                        }
                    case _length:
                        {
                            return length((String)_params[0]);
                        }
                    case _locate:
                        {
                            return (locate((String)_params[0],
                                                           (String)_params[1],
                                                           (int)_params[2]));
                        }
                    case _log10:
                        {
                            return
                                log10(((double)_params[0]));
                        }
                    case _ltrim:
                        {
                            return ltrim((String)_params[0]);
                        }
                    case _minute:
                        {
                            return minute((DateTime)_params[0]);
                        }
                    case _mod:
                        {
                            return (
                                mod(
                                ((int)_params[0]),
                                ((int)_params[1])));
                        }
                    case _month:
                        {
                            return month((DateTime)_params[0]);
                        }
                    case _monthname:
                        {
                            return (monthname((DateTime)_params[0]));
                        }
                    case _now:
                        {
                            return null;
                        }
                    case _octetLength:
                        {
                            return octetLength((String)_params[0]);
                        }
                    case _position:
                        {
                            return (position((String)_params[0],
                                                             (String)_params[1]));
                        }
                    case _pi:
                        {
                            return piValue;
                        }
                    case _quarter:
                        {
                            return quarter((DateTime)_params[0]);
                        }
                    case _rand:
                        {
                            return rand((int)_params[0]);
                        }
                    case _rawToHex:
                        {
                            return rawToHex((String)_params[0]);
                        }
                    case _repeat:
                        {
                            return repeat((String)_params[0], (int)_params[1]);
                        }
                    case _replace:
                        {
                            return replace((String)_params[0], (String)_params[1],
                                           (String)_params[2]);
                        }
                    case _right:
                        {
                            return right((String)_params[0],
                                         ((int)_params[1]));
                        }
                    case _round:
                        {
                            return
                                round(((double)_params[0]),
                                      ((int)_params[1]));
                        }
                    case _roundMagic:
                        {
                            return
                                roundMagic(((double)_params[0]));
                        }
                    case _rtrim:
                        {
                            return rtrim((String)_params[0]);
                        }
                    case _second:
                        {
                            return second((DateTime)_params[0]);
                        }
                    case _sign:
                        {
                            return (
                                sign(((int)_params[0])));
                        }
                    case _soundex:
                        {
                            return soundex((String)_params[0]);
                        }
                    case _space:
                        {
                            return space(((int)_params[0]));
                        }
                    case _substring:
                        {
                            return substring((String)_params[0],
                                             ((int)_params[1]),
                                             (int)_params[2]);
                        }
                    case _trim:
                        {
                            return trim((String)_params[0], (String)_params[1],
                                        ((bool)_params[2]),
                                        ((bool)_params[3]));
                        }
                    case _truncate:
                        {
                            return
                                truncate(
                                    ((double)_params[0]),
                                    ((int)_params[1]));
                        }
                    case _ucase:
                        {
                            return ucase((String)_params[0]);
                        }
                    case _user:
                        {
                            return null;
                        }
                    case _week:
                        {
                            return week((DateTime)_params[0]);
                        }
                    case _year:
                        {
                            return year((DateTime)_params[0]);
                        }
                    case _to_char:
                        {
                            return to_char((DateTime)_params[0],
                                           (String)_params[1]);
                        }
                    case _isReadOnlyDatabaseFiles:
                        {
                            return null;
                        }
                    default:
                        {

                            // coding error
                            Trace.doAssert(false);

                            return null;
                        }
                }
            }
            catch (Exception e)
            {
                throw Trace.error(Trace.FUNCTION_CALL_ERROR, e.ToString());
            }
        }

        public const string prefix = "EffiProzDB.Library.";
        public static readonly int prefixLength = prefix.Length;

        public static int functionID(string fname)
        {
            if (fname.StartsWith(prefix))
            {
                int fid;
                if (!functionMap.TryGetValue( fname.Substring(prefixLength), out fid))
                {
                    fid = -1;
                }

                return fid;
            }

            return -1;   
        }
    }
}